﻿using DBUtil.MetaData;
using DBUtil.Provider.SqlServer.MetaData;
using DotNetCommon;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Index = DBUtil.MetaData.Index;

namespace DBUtil.Provider.SqlServer;

public partial class SqlServerManage : DBManage
{
    #region ShowSchemasAsync
    private static readonly Dictionary<string, string> SchemaRemarkDic = new()
    {
        { "dbo","全称：Database Owner（数据库所有者）。默认架构，几乎所有系统对象和未指定架构的用户对象都默认属于 dbo"},
        { "guest","guest 架构用于临时访问数据库的用户（未映射到数据库用户的登录名）。默认情况下被禁用（需手动启用）。另外: guest 既是架构也是数据库用户"},
        { "INFORMATION_SCHEMA","存储数据库元数据的系统架构。遵循 SQL 标准，所有关系型数据库（如 MySQL、PostgreSQL）都有类似的 INFORMATION_SCHEMA，方便跨数据库查询元数据"},
        { "sys","SQL Server 内部管理的架构，存储数据库的元数据（如表结构、索引、权限配置等）"},
        { "db_owner","数据库所有者角色，是数据库中权限最高的角色，拥有数据库的全部权限（包括创建、修改、删除对象，管理权限等），默认包含 dbo 用户。"},
        { "db_accessadmin","管理数据库访问权限的角色。可添加 / 删除数据库用户、角色、应用程序角色，控制谁能访问数据库。"},
        { "db_securityadmin","管理数据库安全权限的角色。可授予 / 撤销数据库对象的权限，管理角色成员关系，查看审计信息。"},
        { "db_ddladmin","管理数据库对象定义的角色。可创建、修改、删除数据库对象（表、视图、存储过程等），但不能管理数据（如插入、删除数据）。"},
        { "db_backupoperator","管理数据库备份的角色。可执行数据库备份操作，但不能恢复备份或修改数据库结构。"},
        { "db_datareader","读取数据库数据的角色。对数据库中所有用户表拥有 SELECT 权限（可读取数据，但不能修改）。"},
        { "db_datawriter","修改数据库数据的角色。对数据库中所有用户表拥有 INSERT、UPDATE、DELETE 权限（可修改数据，但不能创建 / 删除表）。"},
        { "db_denydatareader","拒绝读取数据库数据的角色（与 db_datareader 相反）。即使被授予了 SELECT 权限，该角色成员也无法读取任何用户表的数据（“拒绝” 权限优先于 “授予” 权限）。"},
        { "db_denydatawriter","拒绝修改数据库数据的角色（与 db_datawriter 相反）。即使被授予了 INSERT/UPDATE/DELETE 权限，该角色成员也无法修改任何用户表的数据。"},
    };
    public override async Task<List<Schema>> ShowSchemasAsync()
    {
        var sql = """
            select t.CATALOG_NAME,t.SCHEMA_NAME,t.SCHEMA_OWNER,t.DEFAULT_CHARACTER_SET_NAME,t2.schema_id,t2.principal_id,t.DEFAULT_CHARACTER_SET_CATALOG,t.DEFAULT_CHARACTER_SET_SCHEMA 
            from INFORMATION_SCHEMA.SCHEMATA t 
            	left join sys.schemas t2 on schema_id(t.SCHEMA_NAME)=t2.schema_id
            """;
        //sqlserver2008 下的 [test2] 数据查询的示例:
        // CATALOG_NAME    SCHEMA_NAME        SCHEMA_OWNER          DEFAULT_CHARACTER_SET_NAME    schema_id    principal_id   DEFAULT_CHARACTER_SET_CATALOG    DEFAULT_CHARACTER_SET_SCHEMA
        // test2           dbo                dbo                   cp936                         1            1              NULL                             NULL                        
        // test2           guest              guest                 cp936                         2            2              NULL                             NULL                        
        // test2           INFORMATION_SCHEMA INFORMATION_SCHEMA    cp936                         3            3              NULL                             NULL                        
        // test2           sys                sys                   cp936                         4            4              NULL                             NULL                        
        // test2           db_owner           db_owner              cp936                         16384        16384          NULL                             NULL                        
        // test2           db_accessadmin     db_accessadmin        cp936                         16385        16385          NULL                             NULL                        
        // test2           db_securityadmin   db_securityadmin      cp936                         16386        16386          NULL                             NULL                        
        // test2           db_ddladmin        db_ddladmin           cp936                         16387        16387          NULL                             NULL                        
        // test2           db_backupoperator  db_backupoperator     cp936                         16389        16389          NULL                             NULL                        
        // test2           db_datareader      db_datareader         cp936                         16390        16390          NULL                             NULL                        
        // test2           db_datawriter      db_datawriter         cp936                         16391        16391          NULL                             NULL                        
        // test2           db_denydatareader  db_denydatareader     cp936                         16392        16392          NULL                             NULL                        
        // test2           db_denydatawriter  db_denydatawriter     cp936                         16393        16393          NULL                             NULL                        
        var dt = await db.SelectDataTableAsync(sql);
        var res = new List<Schema>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var item = new SqlServerSchema()
            {
                DataBaseName = dt.Rows[i]["CATALOG_NAME"].ToString(),
                Name = dt.Rows[i]["SCHEMA_NAME"].ToString(),
                Owner = dt.Rows[i]["SCHEMA_OWNER"].ToString(),
                DEFAULT_CHARACTER_SET_NAME = dt.Rows[i]["DEFAULT_CHARACTER_SET_NAME"]?.ToString(),
                SchemaId = int.Parse(dt.Rows[i]["schema_id"].ToString()),
                PrincipalId = int.Parse(dt.Rows[i]["principal_id"].ToString()),
                DEFAULT_CHARACTER_SET_CATALOG = dt.Rows[i]["DEFAULT_CHARACTER_SET_CATALOG"]?.ToString(),
                DEFAULT_CHARACTER_SET_SCHEMA = dt.Rows[i]["DEFAULT_CHARACTER_SET_SCHEMA"]?.ToString(),
            };
            res.Add(item);
            if (SchemaRemarkDic.TryGetValue(item.Name, out var remark)) item.Remark = remark;
        }
        return res;
    }
    #endregion

    #region 表的元数据
    /// <inheritdoc/>
    public async override Task<List<Table>> ShowTablesAsync(string schemaPureName = null) => await ShowTablesAsync(schemaPureName, null);
    private string GetTableSpaceUsedSql(string schemaPureName = null, string tablePureName = null)
    {
        return
            $"""
            -- 从 sp_helptext sp_spaceused 得知单个表空间查询, 又经 AI 封装
            if object_id(N'tempdb..#TableSpaceInfo',N'U') is not null
                DROP table #TableSpaceInfo;
            -- 创建临时表存储结果
            CREATE TABLE #TableSpaceInfo (
                SchemaName NVARCHAR(128),
                TableName NVARCHAR(128),
                Rows CHAR(11),
                Reserved NVARCHAR(18),
                Data NVARCHAR(18),
                IndexSize NVARCHAR(18),
                Unused NVARCHAR(18)
            );

            -- 声明变量
            DECLARE 
                @objname nvarchar(776),
                @id int,
                @type character(2),
                @pages bigint,
                @reservedpages bigint,
                @usedpages bigint,
                @rowCount bigint,
                @schemaName NVARCHAR(128),
                @tableName NVARCHAR(128);

            -- 声明游标
            DECLARE tableCursor CURSOR FOR
                SELECT 
                    s.name AS SchemaName,
                    t.name AS TableName,
                    QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS FullTableName
                FROM 
                    sys.tables t
                JOIN 
                    sys.schemas s ON t.schema_id = s.schema_id
                WHERE 
                    1 = 1
                    -- 如何开启cdc? sqlserver2008 实测成功 https://www.cnblogs.com/dakai620/p/15801576.html
                    -- and t.is_ms_shipped = 0  -- 排除系统表(先不排除, 因为 cdc 产生的约7张表 的此标记是1)
                    {(schemaPureName.IsNotNullOrWhiteSpace() ? $"and s.name = {db.ProtectStringToSeg(schemaPureName)}" : "")}
                    {(tablePureName.IsNotNullOrWhiteSpace() ? $"t.name = {db.ProtectStringToSeg(tablePureName)}" : "")}
                ORDER BY 
                    s.name, t.name;

            -- 打开游标
            OPEN tableCursor;

            -- 获取第一条记录
            FETCH NEXT FROM tableCursor INTO @schemaName, @tableName, @objname;

            -- 循环处理所有表
            WHILE @@FETCH_STATUS = 0
            BEGIN
                -- 初始化变量
                SET @reservedpages = 0;
                SET @usedpages = 0;
                SET @pages = 0;
                SET @rowCount = 0;

                -- 获取对象ID和类型
                SELECT @id = object_id, @type = type 
                FROM sys.objects 
                WHERE object_id = object_id(@objname);

                -- 计算空间占用
                SELECT   
                    @reservedpages = SUM (reserved_page_count),
                    @usedpages = SUM (used_page_count),
                    @pages = SUM (  
                        CASE  
                            WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
                            ELSE lob_used_page_count + row_overflow_used_page_count  
                        END  
                    ),  
                    @rowCount = SUM (  
                        CASE  
                            WHEN (index_id < 2) THEN row_count  
                            ELSE 0  
                        END  
                    )  
                FROM sys.dm_db_partition_stats  
                WHERE object_id = @id;

                -- 检查是否有XML索引或全文索引的内部表
                IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0   
                BEGIN  
                    SELECT   
                        @reservedpages = @reservedpages + sum(reserved_page_count),
                        @usedpages = @usedpages + sum(used_page_count)  
                    FROM sys.dm_db_partition_stats p, sys.internal_tables it  
                    WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;  
                END  

                -- 将结果插入临时表
                INSERT INTO #TableSpaceInfo (SchemaName, TableName, Rows, Reserved, Data, IndexSize, Unused)
                SELECT   
                    @schemaName,
                    @tableName,
                    convert(char(11), @rowCount),
                    LTRIM(STR(@reservedpages * 8, 15, 0) + ' KB'),
                    LTRIM(STR(@pages * 8, 15, 0) + ' KB'),
                    LTRIM(STR((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
                    LTRIM(STR((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB');

                -- 获取下一条记录
                FETCH NEXT FROM tableCursor INTO @schemaName, @tableName, @objname;
            END

            -- 关闭并释放游标
            CLOSE tableCursor;
            DEALLOCATE tableCursor;

            -- 显示结果
            SELECT * FROM #TableSpaceInfo ORDER BY SchemaName, TableName;

            -- 清理临时表
            DROP TABLE #TableSpaceInfo;
            """;
    }
    private async Task<List<Table>> ShowTablesAsync(string schemaPureName = null, string tablePureName = null)
    {
        //不用加db限制
        string sql = """
            select t.*,o.create_date,o.modify_date, e.value 
            from INFORMATION_SCHEMA.TABLES t
            	left join sys.objects o on OBJECT_ID(t.TABLE_NAME)=o.object_id 
            	left join sys.extended_properties e on OBJECT_ID(t.TABLE_NAME)=e.major_id and e.minor_id=0 and e.name='MS_Description'
            where t.TABLE_TYPE='BASE TABLE';
            """;
        if (schemaPureName.IsNotNullOrWhiteSpace()) sql += $"\r\nand t.TABLE_SCHEMA={db.ProtectStringToSeg(schemaPureName)}";
        if (tablePureName.IsNotNullOrWhiteSpace()) sql += $"\r\nand t.TABLE_NAME={db.ProtectStringToSeg(tablePureName)}";
        sql += "\r\n";
        //表使用空间
        sql += GetTableSpaceUsedSql(schemaPureName, tablePureName);

        var ds = await db.SelectDataSetAsync(sql);
        var dt = ds.Tables[0];
        var dt2 = ds.Tables[1];

        //下面都是在开启 cdc 后测试的
        //第一张表在 sqlserver2008 上的结果
        // TABLE_CATALOG    TABLE_SCHEMA    TABLE_NAME          TABLE_TYPE    create_date                modify_date                value
        // test             dbo             test                BASE TABLE    2025-09-11 22:13:04.413    2025-09-14 00:13:35.567    NULL
        // test             dbo             t_user              BASE TABLE    2025-09-13 16:54:58.067    2025-09-13 16:56:37.493    NULL
        // test             sales           customer            BASE TABLE    NULL                       NULL                       NULL
        // test             sales           t_user              BASE TABLE    2025-09-13 16:54:58.067    2025-09-13 16:56:37.493    NULL
        // test             dbo             systranschemas      BASE TABLE    2025-09-14 00:10:04.787    2025-09-14 00:10:04.800    NULL
        // test             cdc             change_tables       BASE TABLE    NULL                       NULL                       NULL
        // test             cdc             ddl_history         BASE TABLE    NULL                       NULL                       NULL
        // test             cdc             lsn_time_mapping    BASE TABLE    NULL                       NULL                       NULL
        // test             cdc             captured_columns    BASE TABLE    NULL                       NULL                       NULL
        // test             cdc             index_columns       BASE TABLE    NULL                       NULL                       NULL
        // test             cdc             test_instance_CT    BASE TABLE    NULL                       NULL                       NULL

        //第二张表在 sqlserver2008 上的结果
        // SchemaName    TableName            Rows    Reserved    Data     IndexSize    Unused
        // cdc           captured_columns     3       16 KB       8 KB     8 KB         0 KB
        // cdc           change_tables        1       48 KB       8 KB     40 KB        0 KB
        // cdc           ddl_history          0       0 KB        0 KB     0 KB         0 KB
        // cdc           index_columns        1       16 KB       8 KB     8 KB         0 KB
        // cdc           lsn_time_mapping     2       32 KB       8 KB     24 KB        0 KB
        // cdc           test_instance_CT     0       0 KB        0 KB     0 KB         0 KB
        // dbo           systranschemas       0       0 KB        0 KB     0 KB         0 KB
        // dbo           t_user               0       0 KB        0 KB     0 KB         0 KB
        // dbo           test                 0       0 KB        0 KB     0 KB         0 KB
        // sales         customer             0       0 KB        0 KB     0 KB         0 KB
        // sales         t_user               1       16 KB       8 KB     8 KB         0 KB

        var tables = new List<Table>();
        for (int i = 0, len = dt.Rows.Count; i < len; i++)
        {
            var tbl = new SqlServerTable()
            {
                SchemaName = dt.Rows[i]["TABLE_SCHEMA"].ToString(),
                Name = dt.Rows[i]["TABLE_NAME"].ToString(),
                Desc = dt.Rows[i]["value"]?.ToString()
            };
            tables.Add(tbl);
            var createTime = dt.Rows[i]["create_date"].ToString();
            if (!string.IsNullOrWhiteSpace(createTime))
            {
                tbl.CreateTime = DateTime.Parse(createTime);
            }
            var updatetime = dt.Rows[i]["modify_date"].ToString();
            if (!string.IsNullOrWhiteSpace(updatetime))
            {
                tbl.LastUpdate = DateTime.Parse(updatetime);
            }
            DataRow datarow = null;
            for (int j = 0; j < dt2.Rows.Count; j++)
            {
                if (dt2.Rows[j]["SchemaName"].ToString() == tbl.SchemaName && dt2.Rows[j]["TableName"].ToString() == tbl.Name)
                {
                    datarow = dt2.Rows[j];
                    break;
                }
            }
            if (datarow != null)
            {
                tbl.RowCount = int.Parse(datarow["Rows"].ToString());
                tbl.ReservedSize = datarow["Reserved"].ToString();
                try
                {
                    if (tbl.ReservedSize.EndsWith(" KB"))
                    {
                        var size = tbl.ReservedSize.Replace(" KB", "").To<int>() * 1024;
                        if (size > 1024) tbl.ReservedSize = DotNetCommon.UnitConverter.Humanize(size);
                    }
                }
                catch { }
                tbl.DataSize = datarow["Data"].ToString();
                tbl.IndexSize = datarow["IndexSize"].ToString();
                tbl.UnUsedSize = datarow["Unused"].ToString();
                #region sql变更数据捕获表自动赋予说明
                if (tbl.Desc.IsNullOrWhiteSpace())
                {
                    if (tbl.SchemaName == "cdc" || tbl.Name == "systranschemas")
                    {
                        tbl.Desc = "[自动说明: 可能是变更数据捕获用表]";
                        if (tbl.SchemaName == "cdc" && tbl.Name.EndsWith("_CT"))
                        {
                            tbl.Desc = "[自动说明: 可能是变更数据捕获实例表]";
                        }
                    }
                }
                #endregion
            }
        }
        return tables;
    }

    public override async Task<List<Table>> ShowCurrentSchemaTablesAsync()
    {
        var schemaPureName = await db.SelectScalarAsync<string>("select SCHEMA_NAME()");
        return await ShowTablesAsync(schemaPureName);
    }
    public override async Task<Table> ShowTableDetailAsync(string tablePureName, string schemaPureName = null, EnumTableDetailType detailType = EnumTableDetailType.All)
    {
        var table = await GetTableSimpleInfoAsync(tablePureName, schemaPureName);
        if (table == null) return null;

        if ((detailType & EnumTableDetailType.All) > 0 || (detailType & EnumTableDetailType.Column) > 0)
        {
            var columns = table.Columns = await GetColumnsAsync(table.Name, table.SchemaName);
            table.PrimaryKey = columns.Where(i => i.IsPrimaryKey).ToStringSeparated(",");
        }
        if ((detailType & EnumTableDetailType.All) > 0 || (detailType & EnumTableDetailType.Trigger) > 0)
            table.Triggers = await GetTriggersAsync(table.Name, table.SchemaName);
        if ((detailType & EnumTableDetailType.All) > 0 || (detailType & EnumTableDetailType.Constraint) > 0)
            table.Constraints = await GetConstraintsAsync(table.Name, table.SchemaName);
        if ((detailType & EnumTableDetailType.All) > 0 || (detailType & EnumTableDetailType.Index) > 0)
            table.Indexes = await GetIndexesAsync(table.Name, table.SchemaName);
        ModifyColProperties(table);
        ModifyTableProperties(table);
        return table;
    }
    private async Task<SqlServerTable> GetTableSimpleInfoAsync(string tablePureName, string schemaPureName = null)
        => (await ShowTablesAsync(schemaPureName, tablePureName)).FirstOrDefault() as SqlServerTable;

    private async Task<List<Column>> GetColumnsAsync(string tablePureName, string schemaPureName = null, string columnPureName = null)
    {
        (schemaPureName, tablePureName, string fullQuoteName, string fullNameForSeg) = await FixTableOrViewName(schemaPureName, tablePureName);
        var sql = $"""
            select 
            	序号=ROW_NUMBER() OVER(order BY ORDINAL_POSITION),
                表名=t.TABLE_NAME,
            	列名=t.COLUMN_NAME,
            	类型=t.DATA_TYPE,
                t.CHARACTER_MAXIMUM_LENGTH,
            	t.NUMERIC_PRECISION,
            	t.NUMERIC_PRECISION_RADIX,
            	t.NUMERIC_SCALE,
            	t.DATETIME_PRECISION,
            	说明=tt.说明,
            	是否可空=t.IS_NULLABLE,
                自增种子=IDENT_SEED({fullNameForSeg}),
                增量=IDENT_INCR({fullNameForSeg}),
            	默认值=t.COLUMN_DEFAULT,
            	是否自增=case (COLUMNPROPERTY(      
                  OBJECT_ID('test.name'),COLUMN_NAME,'IsIdentity')) when 1 then 'Yes' else 'No' end,
                tt.is_computed,
                tt.definition,
                tt.is_persisted    
            from INFORMATION_SCHEMA.COLUMNS t
            --关联列说明
            left outer join
            	(select 列名=c.name,说明=p.value ,cc.is_computed,cc.definition,cc.is_persisted
            	from sys.columns c 
            		left outer join sys.computed_columns cc on c.object_id=cc.object_id and c.column_id=cc.column_id
            		left outer join sys.extended_properties p on c.column_id = p.minor_id and c.object_id=p.major_id
            	where c.object_id=OBJECT_ID({fullNameForSeg})
            	)tt
            	on t.COLUMN_NAME=tt.列名
            where t.TABLE_NAME={db.ProtectStringToSeg(tablePureName)}
            and t.TABLE_SCHEMA={db.ProtectStringToSeg(schemaPureName)}
            {(columnPureName.IsNotNullOrWhiteSpace() ? $"and t.COLUMN_NAME={db.ProtectStringToSeg(columnPureName)}" : "")}
            """;
        var dt = await db.SelectDataTableAsync(sql);
        var columns = new List<Column>();
        if (dt.Rows.Count > 0)
        {
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var column = new SqlServerColumn();
                column.TableName = dt.Rows[i]["表名"].ToString();
                column.Name = dt.Rows[i]["列名"].ToString();
                //列类型
                column.DATA_TYPE = dt.Rows[i]["类型"].ToString();
                column.CHARACTER_MAXIMUM_LENGTH = dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                column.NUMERIC_PRECISION = dt.Rows[i]["NUMERIC_PRECISION"].ToString();
                column.DATETIME_PRECISION = dt.Rows[i]["DATETIME_PRECISION"].ToString();
                column.NUMERIC_SCALE = dt.Rows[i]["NUMERIC_SCALE"].ToString();
                column.ConvertType();
                //列说明
                column.Desc = dt.Rows[i]["说明"].ToString();
                //是否可空
                column.IsNullAble = dt.Rows[i]["是否可空"].ToString() == "NO" ? false : true;
                //自增
                column.IsIdentity = dt.Rows[i]["是否自增"].ToString() == "No" ? false : true;
                if (column.IsIdentity)
                {
                    column.IdentityStart = int.Parse(dt.Rows[i]["自增种子"].ToString());
                    column.IdentityIncre = int.Parse(dt.Rows[i]["增量"].ToString());
                }
                //默认值
                var de = dt.Rows[i]["默认值"].ToString();
                column.HasDefault = !string.IsNullOrEmpty(de);
                //计算列
                column.IsComputed = dt.Rows[i]["is_computed"].ToString() == "True";
                if (column.IsComputed)
                {
                    column.ComputedDefinition = dt.Rows[i]["definition"].ToString();
                    column.IsPersisted = dt.Rows[i]["is_persisted"].ToString() == "True";
                }
                if (column.HasDefault) column.Default = dt.Rows[i]["默认值"].ToString();
                columns.Add(column);
                //唯一:通过唯一约束修正
            }
        }
        sql = $@"
select b.column_name
from information_schema.table_constraints a
inner join information_schema.constraint_column_usage b
on a.constraint_name = b.constraint_name
where a.constraint_type = 'PRIMARY KEY' and a.table_name = {db.ProtectStringToSeg(tablePureName)} and a.TABLE_SCHEMA={db.ProtectStringToSeg(schemaPureName)}
";
        dt = await db.SelectDataTableAsync(sql);
        if (dt.Rows.Count > 0)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string colname = dt.Rows[i][0].ToString();
                var col = columns.First<Column>(j => j.Name == colname);
                col.IsPrimaryKey = true;
                if (dt.Rows.Count == 1)
                {
                    //主键就一列  肯定是unique
                    col.IsUnique = true;
                }
            }
        }
        return columns;
    }

    private async Task<List<DBUtil.MetaData.Constraint>> GetConstraintsAsync(string tablePureName, string schemaPureName = null)
    {
        (schemaPureName, tablePureName, string fullQuoteName, string fullNameForSeg) = await FixTableOrViewName(schemaPureName, tablePureName);

        var constraints = new List<DBUtil.MetaData.Constraint>();
        var sql = "";
        //查询默认约束
        sql = $@"
select 
SCHEMA_NAME(sysd.schema_id) as schema_name,
syst.name as table_name,
sysd.name,
sysd.type,sysd.type_desc,
sysc.name as column_name,
sysd.create_date,sysd.modify_date,
sysd.is_system_named,sysd.definition 
from sys.default_constraints sysd 
left join sys.tables syst on syst.object_id=sysd.parent_object_id
left join sys.columns sysc on syst.object_id=sysc.object_id and sysc.column_id=sysd.parent_column_id
where sysd.type='D' 
and sysd.parent_object_id=OBJECT_ID({fullNameForSeg})
";
        var dt = db.SelectDataTable(sql);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var cons = new SqlServerConstraint()
            {
                SchemaName = dt.Rows[i]["schema_name"].ToString(),
                TableName = dt.Rows[i]["table_name"].ToString(),
                Name = dt.Rows[i]["name"].ToString(),
                Type = EnumConstraintType.DefaultValue,
                ConstraintKeys = dt.Rows[i]["column_name"].ToString(),
                IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True",
                Definition = dt.Rows[i]["definition"].ToString()
            };
            var createTime = dt.Rows[i]["create_date"].ToString();
            if (!string.IsNullOrWhiteSpace(createTime))
            {
                cons.CreateTime = DateTime.Parse(createTime);
            }
            if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
            {
                cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
            }
            constraints.Add(cons);
        }
        //查询检查约束
        sql = $@"
select 
	SCHEMA_NAME(sysc.schema_id) as schema_name,
	syst.name as table_name, 
	sysc.name,
	sysc.create_date,sysc.modify_date,
	sysc.type,sysc.type_desc,
	sysc.definition,sysc.is_system_named
from sys.check_constraints sysc left join sys.tables syst on sysc.parent_object_id=syst.object_id
where 
	sysc.parent_object_id =OBJECT_ID({fullNameForSeg})";
        dt = db.SelectDataTable(sql);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var cons = new SqlServerConstraint()
            {
                SchemaName = dt.Rows[i]["schema_name"].ToString(),
                TableName = dt.Rows[i]["table_name"].ToString(),
                Name = dt.Rows[i]["name"].ToString(),
                Type = EnumConstraintType.Check,
                IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True",
                Definition = dt.Rows[i]["definition"].ToString()
            };

            var createTime = dt.Rows[i]["create_date"].ToString();
            if (!string.IsNullOrWhiteSpace(createTime))
            {
                cons.CreateTime = DateTime.Parse(createTime);
            }
            if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
            {
                cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
            }
            constraints.Add(cons);
        }
        //查询主键约束和唯一约束
        sql = $@"
select 
	sysk.object_id,SCHEMA_NAME(sysk.schema_id) as schema_name,sysk.name as name, syst.name as table_name,sysc.name as col_name,
	sysk.type,sysk.type_desc,
	sysk.create_date,sysk.modify_date,
	sysk.is_system_named
from sys.key_constraints sysk 
	left join sys.indexes sysi on sysk.name =sysi.name
	left join sys.index_columns sysic on sysi.object_id=sysic.object_id and sysi.index_id=sysic.index_id
	left join sys.columns sysc on sysic.object_id=sysc.object_id and sysic.column_id=sysc.column_id
	left join sys.tables syst on sysi.object_id=syst.object_id
where sysk.parent_object_id =object_id({fullNameForSeg})
order by sysk.object_id";
        dt = db.SelectDataTable(sql);
        int lastobjid = -1;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var objid = int.Parse(dt.Rows[i]["object_id"].ToString());
            var colname = dt.Rows[i]["col_name"].ToString();
            if (lastobjid == objid)
            {
                (constraints.Last() as SqlServerConstraint).ConstraintKeys += "," + colname;
                continue;
            }
            lastobjid = objid;
            var cons = new SqlServerConstraint()
            {
                SchemaName = dt.Rows[i]["schema_name"].ToString(),
                TableName = dt.Rows[i]["table_name"].ToString(),
                Name = dt.Rows[i]["name"].ToString(),
                ConstraintKeys = colname,
                Type = dt.Rows[i]["type"].ToString() == "PK" ? EnumConstraintType.PrimaryKey : EnumConstraintType.Unique,
                IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True"
            };
            var createTime = dt.Rows[i]["create_date"].ToString();
            if (!string.IsNullOrWhiteSpace(createTime))
            {
                cons.CreateTime = DateTime.Parse(createTime);
            }
            if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
            {
                cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
            }
            constraints.Add(cons);
        }
        //查询外键约束
        sql = $@"
select 
	SCHEMA_NAME(sysfk.schema_id) as schema_name,
	sysfk.name,
	srctablename=(select name from sys.tables syst where sysfkc.parent_object_id=syst.object_id),
	srccolname=(select sysc.name from sys.columns sysc where sysfkc.parent_column_id=sysc.column_id and sysfkc.parent_object_id=sysc.object_id),
	desttablename=(select name from sys.tables syst where sysfkc.referenced_object_id=syst.object_id),
	destcolname=(select sysc.name from sys.columns sysc where sysfkc.referenced_column_id=sysc.column_id and sysfkc.referenced_object_id=sysc.object_id),
	sysfk.create_date,
	sysfk.modify_date,
	sysfk.is_system_named,
	sysfk.delete_referential_action,
	sysfk.delete_referential_action_desc,
	sysfk.update_referential_action,
	sysfk.update_referential_action_desc
from sys.foreign_keys sysfk 
	left join sys.foreign_key_columns sysfkc on sysfk.object_id=sysfkc.constraint_object_id
	left join sys.tables syst on sysfk.parent_object_id=syst.object_id
	left join sys.tables systr on sysfk.referenced_object_id=systr.object_id
	left join sys.columns sysc on sysc.object_id =sysfk.parent_object_id and sysfk.key_index_id=sysc.column_id
where sysfk.parent_object_id =object_id({fullNameForSeg})";
        dt = db.SelectDataTable(sql);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var cons = new SqlServerConstraint()
            {
                SchemaName = dt.Rows[i]["schema_name"].ToString(),
                TableName = dt.Rows[i]["srctablename"].ToString(),
                Name = dt.Rows[i]["name"].ToString(),
                ConstraintKeys = dt.Rows[i]["srccolname"].ToString(),
                ReferenceTableName = dt.Rows[i]["desttablename"].ToString(),
                ReferenceColumnName = dt.Rows[i]["destcolname"].ToString(),
                Type = EnumConstraintType.ForeignKey,
                IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True",
                Delete_Action = dt.Rows[i]["delete_referential_action_desc"].ToString(),
                Update_Action = dt.Rows[i]["update_referential_action_desc"].ToString()
            };
            var createTime = dt.Rows[i]["create_date"].ToString();
            if (!string.IsNullOrWhiteSpace(createTime))
            {
                cons.CreateTime = DateTime.Parse(createTime);
            }
            if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
            {
                cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
            }
            constraints.Add(cons);
        }
        return constraints;
    }

    private async Task<List<Index>> GetIndexesAsync(string tablePureName, string schemaPureName = null)
    {
        (schemaPureName, tablePureName, string fullQuoteName, string fullNameForSeg) = await FixTableOrViewName(schemaPureName, tablePureName);
        var indexs = new List<Index>();
        string sql = $@"
select 
OBJECT_SCHEMA_NAME(sysi.object_id) as 'schema',
sysd.name  as 'data_space_name', 
sysd.type as 'data_space_type',
sysd.type_desc as 'data_space_type_desc',
sysi.name as '索引名称', 
syst.name as '表名',
sysc.name as '列名',
sysi.type_desc as '索引类型',
sysi.is_unique as '索引是否要求唯一',
sysc.is_nullable as '索引是否允许为空',
sysi.is_primary_key as '是否是主键约束',
sysi.is_unique_constraint as '是否是唯一约束',
sysk.create_date,
sysk.modify_date
from sys.indexes sysi 
left join sys.key_constraints sysk on sysi.object_id=sysk.parent_object_id and sysi.name=sysk.name
left join sys.index_columns sysic on sysi.object_id=sysic.object_id and sysi.index_id=sysic.index_id
left join sys.tables syst on sysi.object_id=syst.object_id
left join sys.columns sysc on sysi.object_id=sysc.object_id and sysic.column_id=sysc.column_id
left join sys.data_spaces sysd on sysi.data_space_id=sysd.data_space_id

where sysi.object_id=OBJECT_ID({fullNameForSeg})
order by sysi.name
";
        var dt = db.SelectDataTable(sql);
        if (dt != null && dt.Rows.Count > 0)
        {
            SqlServerIndex lastindex = null;
            for (int i = dt.Rows.Count - 1; i >= 0; i--)
            {
                var tablename = dt.Rows[i]["表名"].ToString();
                var colname = dt.Rows[i]["列名"].ToString();
                var indexname = dt.Rows[i]["索引名称"].ToString();
                if (lastindex != null && lastindex.TableName == tablename && lastindex.Name == indexname)
                {
                    lastindex.ColumnName += "," + colname;
                    continue;
                }
                else if (lastindex != null)
                {
                    indexs.Add(lastindex);
                }
                lastindex = new SqlServerIndex()
                {
                    DataBaseName = db.DBName,
                    SchemaName = dt.Rows[i]["schema"].ToString(),
                    Name = dt.Rows[i]["索引名称"].ToString(),
                    DataSpaceName = dt.Rows[i]["data_space_name"].ToString(),
                    DataSpaceType = dt.Rows[i]["data_space_type"].ToString(),
                    DataSpaceTypeDesc = dt.Rows[i]["data_space_type_desc"].ToString(),
                    TableName = dt.Rows[i]["表名"].ToString(),
                    ColumnName = dt.Rows[i]["列名"].ToString(),
                    IsClustered = dt.Rows[i]["索引类型"].ToString() == "CLUSTERED",
                    IndexType = dt.Rows[i]["索引类型"].ToString(),
                    IsNullAble = dt.Rows[i]["索引是否允许为空"].ToString() == "True",
                    IsUnique = dt.Rows[i]["索引是否要求唯一"].ToString() == "True",
                    IsPrimaryKey = dt.Rows[i]["是否是主键约束"].ToString() == "True",
                    IsUniqueKey = dt.Rows[i]["是否是唯一约束"].ToString() == "True"
                };
                if (!string.IsNullOrWhiteSpace(dt.Rows[i]["create_date"].ToString()))
                {
                    lastindex.CreateTime = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                }
                if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                {
                    lastindex.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                }
            }
            indexs.Add(lastindex);
        }
        return indexs;
    }

    private async Task<List<Trigger>> GetTriggersAsync(string tablePureName, string schemaPureName = null)
    {
        (schemaPureName, tablePureName, string fullQuoteName, string fullNameForSeg) = await FixTableOrViewName(schemaPureName, tablePureName);

        var triggers = new List<Trigger>();
        var sql = $@"
select syst.name as tablename, 
	systri.name as trigger_name,
	trigger_owner = user_name(ObjectProperty(systri.object_id, 'ownerid')),
	trigger_schema = schema_name(syso.schema_id),
	systri.create_date,
	systri.modify_date,
	systri.is_disabled,
	systri.is_not_for_replication,
	systri.is_instead_of_trigger,	
	isupdate = ObjectProperty( systri.object_id, 'ExecIsUpdateTrigger'),
	isdelete = ObjectProperty( systri.object_id, 'ExecIsDeleteTrigger'),
	isinsert = ObjectProperty( systri.object_id, 'ExecIsInsertTrigger'),
	isafter = ObjectProperty( systri.object_id, 'ExecIsAfterTrigger'),
	isinsteadof = ObjectProperty( systri.object_id, 'ExecIsInsteadOfTrigger'),
	createsql=OBJECT_DEFINITION(systri.object_id)	
from sys.triggers systri 
left join sys.tables syst on systri.parent_id=syst.object_id
left join sys.objects syso on systri.object_id=syso.object_id
where systri.parent_id=object_id({fullNameForSeg})
";
        var dt = await db.SelectDataTableAsync(sql);
        for (int i = 0, len = dt.Rows.Count; i < len; i++)
        {
            string name = dt.Rows[i]["trigger_name"].ToString();
            bool isupdate = dt.Rows[i]["isupdate"].ToString() != "0";
            bool isdelete = dt.Rows[i]["isdelete"].ToString() != "0";
            bool isinsert = dt.Rows[i]["isinsert"].ToString() != "0";
            bool isafter = dt.Rows[i]["isafter"].ToString() != "0";
            bool isinsteadof = dt.Rows[i]["isInsteadof"].ToString() != "0";
            bool is_disabled = dt.Rows[i]["is_disabled"].ToString() != "False";
            bool is_not_for_replication = dt.Rows[i]["is_not_for_replication"].ToString() != "False";
            string createsql = dt.Rows[i]["createsql"].ToString();
            Trigger tri = new SqlServerTrigger()
            {
                Name = name,
                CreateSql = createsql,
                TableName = tablePureName,
                IsInsert = isinsert,
                IsUpdate = isupdate,
                IsDelete = isdelete,
                IsAfter = isafter,
                IsInsteadof = isinsteadof,
                IsDisable = is_disabled,
                IsNotForReplication = is_not_for_replication,
            };
            var createTime = dt.Rows[0]["create_date"].ToString();
            if (!string.IsNullOrWhiteSpace(createTime))
            {
                tri.CreateTime = DateTime.Parse(createTime);
            }
            var updatetime = dt.Rows[0]["modify_date"].ToString();
            if (!string.IsNullOrWhiteSpace(updatetime))
            {
                tri.LastUpdate = DateTime.Parse(updatetime);
            }
            triggers.Add(tri);
        }
        return triggers;
    }

    /// <summary>
    /// 根据表的约束和索引信息,修改表的列信息
    /// </summary>
    /// <param name="table">表</param>
    private void ModifyColProperties(Table table)
    {
        if (table.Columns.IsNullOrEmpty()) return;
        if (table.Constraints.IsNotNullOrEmpty())
        {
            for (var i = 0; i < table.Constraints.Count; i++)
            {
                var constraint = table.Constraints[i] as SqlServerConstraint;
                if (constraint.Type == EnumConstraintType.Unique)
                {
                    if (!constraint.ConstraintKeys.Contains(','))
                    {
                        var column = table.Columns.FirstOrDefault(col => col.Name == constraint.ConstraintKeys);
                        column.IsUnique = true;
                    }
                }
            }
        }
    }

    /// <summary>
    /// 根据索引信息修改表属性
    /// </summary>
    /// <param name="table"></param>
    private void ModifyTableProperties(SqlServerTable table)
    {
        SqlServerIndex index = null;
        index = table.Indexes.FirstOrDefault(i => i.IsClustered || i.IndexType == "HEAP") as SqlServerIndex;
        if (index != null)
        {
            table.DataSpaceName = index.DataSpaceName;
            table.DataSpaceType = index.DataSpaceType;
            table.DataSpaceTypeDesc = index.DataSpaceTypeDesc;
        }
    }

    /// <inheritdoc />
    public override async Task<List<DBUtil.MetaData.Constraint>> ShowTableConstraintsAsync(string tablePureName, string schemaPureName = null)
    {
        return await GetConstraintsAsync(tablePureName, schemaPureName);
    }

    /// <inheritdoc />
    public override async Task<List<Index>> ShowTableIndexesAsync(string tablePureName, string schemaPureName = null)
    {
        return await GetIndexesAsync(tablePureName, schemaPureName);
    }

    /// <inheritdoc />
    public override async Task<List<Column>> ShowTableColumnsAsync(string tablePureName, string schemaPureName = null)
    {
        return await GetColumnsAsync(tablePureName, schemaPureName);
    }

    /// <inheritdoc />
    public override async Task<List<Trigger>> ShowTableTriggersAsync(string tablePureName, string schemaPureName = null)
    {
        return await GetTriggersAsync(tablePureName, schemaPureName);
    }
    #endregion

    #region 视图元数据
    /// <inheritdoc />
    public override async Task<List<View>> ShowViewsAsync(string schemaPureName = null) => await ShowViewsAsync(schemaPureName);

    private async Task<List<View>> ShowViewsAsync(string viewPureName = null, string schemaPureName = null)
    {
        List<View> views = new List<View>();
        var sql = @"select infov.*,sysv.create_date,sysv.modify_date 
from INFORMATION_SCHEMA.VIEWS infov 
	left join sys.views sysv on infov.TABLE_NAME=sysv.name and infov.TABLE_SCHEMA=(select name from sys.schemas where schema_id=sysv.schema_id)
where sysv.type='V'";
        if (viewPureName.IsNotNullOrWhiteSpace()) sql += $" and TABLE_NAME={db.ProtectStringToSeg(viewPureName)}";
        if (schemaPureName.IsNotNullOrWhiteSpace()) sql += $" and TABLE_SCHEMA={db.ProtectStringToSeg(viewPureName)}";
        var dt = await db.SelectDataTableAsync(sql);
        for (int i = 0, len = dt.Rows.Count; i < len; i++)
        {
            var view = new SqlServerView()
            {
                DataBaseName = dt.Rows[i]["TABLE_CATALOG"].ToString(),
                Name = dt.Rows[i]["TABLE_NAME"].ToString(),
                CreateSql = dt.Rows[i]["VIEW_DEFINITION"].ToString(),
                SchemaName = dt.Rows[i]["TABLE_SCHEMA"].ToString()
            };
            var createTime = dt.Rows[i]["create_date"].ToString();
            if (!string.IsNullOrWhiteSpace(createTime))
            {
                view.CreateTime = DateTime.Parse(createTime);
            }
            var updatetime = dt.Rows[i]["modify_date"].ToString();
            if (!string.IsNullOrWhiteSpace(updatetime))
            {
                view.LastUpdate = DateTime.Parse(updatetime);
            }
            views.Add(view);
        }
        if (views.Count > 0)
        {
            //视图说明
            sql = views.Select(v => $"SELECT objname='{v.SchemaName}.'+t.objname,t.value FROM fn_listextendedproperty ('MS_Description', 'schema', '{v.SchemaName}', 'view', '{v.Name}',null,null) t")
                   .ToStringSeparated("\r\nunion\r\n");
            dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var viewname = dt.Rows[i]["objname"].ToString();
                var desc = dt.Rows[i]["value"]?.ToString();
                var v = views.FirstOrDefault(v => $"{v.SchemaName}.{v.Name}" == viewname);
                ((SqlServerView)v).Desc = desc;
            }
        }
        return views;
    }

    /// <inheritdoc />
    public override async Task<List<View>> ShowCurrentSchemaViewsAsync()
    {
        var schemaPureName = await db.SelectScalarAsync<string>("select SCHEMA_NAME()");
        return await ShowViewsAsync(schemaPureName);
    }

    /// <inheritdoc />
    public override async Task<View> ShowViewDetailAsync(string viewPureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(viewPureName);
        var view = (await ShowViewsAsync(viewPureName, schemaPureName)).FirstOrDefault();
        if (view == null) return null;

        (schemaPureName, viewPureName, string fullQuoteName, string fullNameForSeg) = await FixTableOrViewName(schemaPureName, viewPureName);
        string sql = $@"
select 
	序号=ROW_NUMBER() OVER(order BY ORDINAL_POSITION),
	列名=T.COLUMN_NAME,
	类型=T.DATA_TYPE,
    T.CHARACTER_MAXIMUM_LENGTH,
	T.NUMERIC_PRECISION,
	T.NUMERIC_PRECISION_RADIX,
	T.NUMERIC_SCALE,
	T.DATETIME_PRECISION,
    是否可空=T.IS_NULLABLE,
	说明=tt.说明  
from INFORMATION_SCHEMA.COLUMNS T
--关联列说明
left outer join
(select 列名=c.name,说明=p.value
from sys.columns c 
	left outer join sys.extended_properties p on c.column_id = p.minor_id and c.object_id=p.major_id
where c.object_id=OBJECT_ID({fullNameForSeg}))  tt
on t.COLUMN_NAME=tt.列名
where t.TABLE_NAME={db.ProtectStringToSeg(view.Name)} and t.TABLE_SCHEMA={db.ProtectStringToSeg(view.SchemaName)}";
        DataTable dt = db.SelectDataTable(sql);
        var columns = new List<Column>();
        if (dt.Rows.Count > 0)
        {
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var column = new SqlServerColumn();
                column.Name = dt.Rows[i]["列名"].ToString();
                column.TableName = viewPureName;
                //列类型
                column.DATA_TYPE = dt.Rows[i]["类型"].ToString();
                column.CHARACTER_MAXIMUM_LENGTH = dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                column.NUMERIC_PRECISION = dt.Rows[i]["NUMERIC_PRECISION"].ToString();
                column.DATETIME_PRECISION = dt.Rows[i]["DATETIME_PRECISION"].ToString();
                column.NUMERIC_SCALE = dt.Rows[i]["NUMERIC_SCALE"].ToString();
                column.ConvertType();
                //列说明
                column.Desc = dt.Rows[i]["说明"].ToString();
                //是否可空
                column.IsNullAble = dt.Rows[i]["是否可空"].ToString() == "NO" ? false : true;
                columns.Add(column);
            }
        }

        view.Columns = columns;
        return view;
    }
    #endregion

    #region 存储过程&函数&序列
    /// <summary>
    /// 返回所有的序列
    /// </summary>
    /// <param name="schemaName">所属模式名称,如果为空则返回所有</param>
    /// <returns></returns>
    public override async Task<List<Sequence>> ShowSequencesAsync(string schemaName = null)
    {
        if (!db.IsSqlServerVersion2012Compatible())
        {
            //SqlServer 2012推出序列
            return [];
        }

        var res = new List<Sequence>();
        var sql = @"select * from (select 
	SCHEMA_NAME(infos.schema_id) as SEQUENCE_SCHEMA,
	infos.name as SEQUENCE_NAME,
	ISnull(TYPE_NAME(infos.system_type_id),TYPE_NAME(infos.user_type_id)) as DATA_TYPE,
	infos.MINIMUM_VALUE,
	infos.MAXIMUM_VALUE,
	infos.START_VALUE,
	infos.INCREMENT,
	infos.CURRENT_VALUE,
	infos.is_cycling as CYCLE_OPTION,
	infos.IS_CACHED,
	infos.CACHE_SIZE,
infos.create_date,infos.modify_date 
from sys.sequences infos) as infos
where 1=1 ";
        if (schemaName.IsNotNullOrWhiteSpace())
        {
            sql += $" and infos.SEQUENCE_SCHEMA={db.ProtectStringToSeg(schemaName)}";
        }
        var dt = await db.SelectDataTableAsync(sql);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var seq = new SqlServerSequence()
            {
                DataBaseName = db.DBName,
                SchemaName = dt.Rows[i]["SEQUENCE_SCHEMA"].ToString(),
                Name = dt.Rows[i]["SEQUENCE_NAME"].ToString(),
                Type = dt.Rows[i]["DATA_TYPE"].ToString(),
                IsCached = dt.Rows[i]["IS_CACHED"].To<bool>(),
                CacheSize = dt.Rows[i]["CACHE_SIZE"].ToWithDefault<int?>(null),
                Increment = long.Parse(dt.Rows[i]["INCREMENT"].ToString()),
                CurrentValue = long.Parse(dt.Rows[i]["CURRENT_VALUE"].ToString()),
                IsCycle = dt.Rows[i]["CYCLE_OPTION"].ToString() == "True",
                StartValue = long.Parse(dt.Rows[i]["START_VALUE"].ToString()),
                MaxValue = long.Parse(dt.Rows[i]["MAXIMUM_VALUE"].ToString()),
                MinValue = long.Parse(dt.Rows[i]["MINIMUM_VALUE"].ToString())
            };
            var createTime = dt.Rows[i]["create_date"].ToString();
            if (!string.IsNullOrWhiteSpace(createTime))
            {
                seq.CreateTime = DateTime.Parse(createTime);
            }
            var updatetime = dt.Rows[i]["modify_date"].ToString();
            if (!string.IsNullOrWhiteSpace(updatetime))
            {
                seq.LastUpdate = DateTime.Parse(updatetime);
            }
            res.Add(seq);
        }
        return res;
    }

    /// <summary>
    /// 返回所有的存储过程(仅用户自定义的)
    /// </summary>
    /// <param name="schemaName">所属模式名称,如果为空则返回所有</param>
    /// <returns></returns>
    public override async Task<List<Procedure>> ShowProceduresAsync(string schemaName = null)
    {
        var res = new List<Procedure>();
        string sql = @"select infor.ROUTINE_CATALOG, infor.ROUTINE_SCHEMA,infor.ROUTINE_NAME,infor.ROUTINE_DEFINITION,infor.CREATED,infor.LAST_ALTERED
from INFORMATION_SCHEMA.ROUTINES infor where ROUTINE_TYPE='PROCEDURE'";
        if (schemaName.IsNotNullOrWhiteSpace())
        {
            sql += $" and ROUTINE_SCHEMA={db.ProtectStringToSeg(schemaName)}";
        }
        var dt = await db.SelectDataTableAsync(sql);
        if (dt.Rows.Count > 0)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var proc = new SqlServerProcedure()
                {
                    DataBaseName = dt.Rows[i]["ROUTINE_CATALOG"].ToString(),
                    SchemaName = dt.Rows[i]["ROUTINE_SCHEMA"].ToString(),
                    Name = dt.Rows[i]["ROUTINE_NAME"].ToString(),
                    CreateSql = dt.Rows[i]["ROUTINE_DEFINITION"].ToString()
                };
                var createTime = dt.Rows[i]["CREATED"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    proc.CreateTime = DateTime.Parse(createTime);
                }
                var updatetime = dt.Rows[i]["LAST_ALTERED"].ToString();
                if (!string.IsNullOrWhiteSpace(updatetime))
                {
                    proc.LastUpdate = DateTime.Parse(updatetime);
                }
                res.Add(proc);
            }
        }
        if (res.Count > 0)
        {
            //存储过程说明
            sql = res.Select(v => $"SELECT objname='{v.SchemaName}.'+t.objname,t.value FROM fn_listextendedproperty ('MS_Description', 'schema', '{v.SchemaName}', 'PROCEDURE', '{v.Name}',null,null) t")
                   .ToStringSeparated("\r\nunion\r\n");
            dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var viewname = dt.Rows[i]["objname"].ToString();
                var desc = dt.Rows[i]["value"]?.ToString();
                var v = res.FirstOrDefault(v => $"{v.SchemaName}.{v.Name}" == viewname);
                ((SqlServerProcedure)v).Desc = desc;
            }
        }
        return res;
    }

    /// <summary>
    /// 返回所有的函数(仅用户自定义的)
    /// </summary>
    /// <param name="schemaName">所属模式名称,如果为空则返回所有</param>
    /// <returns></returns>
    public override async Task<List<Function>> ShowFunctionsAsync(string schemaName = null)
    {
        var sql = $@"select syso.name,syso.type,syso.type_desc,syso.create_date,syso.modify_date,infor.ROUTINE_CATALOG,infor.ROUTINE_SCHEMA,infor.ROUTINE_DEFINITION
from sys.objects syso left join INFORMATION_SCHEMA.ROUTINES infor on syso.name=infor.ROUTINE_NAME
where type in ('FN','TF','IF')
";
        if (schemaName.IsNotNullOrWhiteSpace())
        {
            sql += $" and ROUTINE_SCHEMA='{schemaName.Replace("'", "''")}'";
        }
        var dt = await db.SelectDataTableAsync(sql);
        var res = new List<Function>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var func = new SqlServerFunction()
            {
                DataBaseName = dt.Rows[i]["ROUTINE_CATALOG"].ToString(),
                SchemaName = dt.Rows[i]["ROUTINE_SCHEMA"].ToString(),
                Name = dt.Rows[i]["name"].ToString(),
                Type = dt.Rows[i]["type"].ToString(),
                TypeString = dt.Rows[i]["type_desc"].ToString(),
                CreateSql = dt.Rows[i]["ROUTINE_DEFINITION"].ToString()
            };
            var createTime = dt.Rows[i]["create_date"].ToString();
            if (!string.IsNullOrWhiteSpace(createTime))
            {
                func.CreateTime = DateTime.Parse(createTime);
            }
            var modify_date = dt.Rows[i]["modify_date"].ToString();
            if (!string.IsNullOrWhiteSpace(modify_date))
            {
                func.LastUpdate = DateTime.Parse(modify_date);
            }
            res.Add(func);
        }
        if (res.Count > 0)
        {
            //函数说明
            sql = res.Select(v => $"SELECT objname='{v.SchemaName}.'+t.objname,t.value FROM fn_listextendedproperty ('MS_Description', 'schema', '{v.SchemaName}', 'FUNCTION', '{v.Name}',null,null) t")
                   .ToStringSeparated("\r\nunion\r\n");
            dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var viewname = dt.Rows[i]["objname"].ToString();
                var desc = dt.Rows[i]["value"]?.ToString();
                var v = res.FirstOrDefault(v => $"{v.SchemaName}.{v.Name}" == viewname);
                ((SqlServerFunction)v).Desc = desc;
            }
        }
        return res;
    }
    #endregion

    #region 显示数据库的备份记录 ShowBackUpHistory()
    /// <summary>
    /// 显示数据库的备份记录
    /// </summary>
    /// <returns></returns>
    public List<SqlServerBackUpHistory> ShowBackUpHistory()
    {
        var sql = $@"
SELECT 
     bs.backup_set_id,
     bs.database_name,
	 bs.name as backup_set_name,
	 bs.user_name,
	 bs.expiration_date,
     bs.backup_start_date,
     bs.backup_finish_date,
     CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
     CAST(DATEDIFF(second, bs.backup_start_date,
     bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
     CASE bs.[type]
         WHEN 'D' THEN 'Full Backup'
         WHEN 'I' THEN 'Differential Backup'
         WHEN 'L' THEN 'TLog Backup'
         WHEN 'F' THEN 'File or filegroup'
         WHEN 'G' THEN 'Differential file'
         WHEN 'P' THEN 'Partial'
         WHEN 'Q' THEN 'Differential Partial'
     END AS BackupType,
     bmf.physical_device_name,
     CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
     CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
     bs.server_name,
     bs.recovery_model
 FROM msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 where database_name='{db.DBName}'
 ORDER BY bs.backup_start_date;";
        return db.SelectModelList<SqlServerBackUpHistory>(sql);
    }
    #endregion

    #region 显示数据库的恢复记录 ShowRestoreHistory()
    /// <summary>
    /// 显示数据库的恢复记录
    /// </summary>
    /// <returns></returns>
    public List<SqlServerRestoreHistory> ShowRestoreHistory()
    {
        var sql = $@"
SELECT 
     rs.[restore_history_id],
     rs.[restore_date],
     rs.[destination_database_name],
     bmf.physical_device_name,
	 bs.name as backup_set_name,
     rs.[user_name],
     rs.[backup_set_id],
     CASE rs.[restore_type]
         WHEN 'D' THEN 'Database'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Log'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'V' THEN 'Verifyonly'
     END AS RestoreType,
     rs.[replace],
     rs.[recovery],
     rs.[restart],
     rs.[stop_at],
     rs.[device_count],
     rs.[stop_at_mark_name],
     rs.[stop_before]
FROM [msdb].[dbo].[restorehistory] rs
INNER JOIN [msdb].[dbo].[backupset] bs
--on rs.backup_set_id = bs.media_set_id
ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf 
ON bs.media_set_id = bmf.media_set_id
where destination_database_name='{db.DBName}'
order by restore_date;";
        return db.SelectModelList<SqlServerRestoreHistory>(sql);
    }
    #endregion

    #region 显示数据库的整体信息 ShowDataBaseInfo()
    /// <summary>
    /// 显示数据库的整体信息
    /// </summary>
    /// <returns></returns>
    public async Task<SqlServerDataBaseInfo> ShowDataBaseInfoAsync()
    {
        var info = new SqlServerDataBaseInfo();
        //磁盘剩余空间
        var dt = await db.SelectDataTableAsync("Exec master.dbo.xp_fixeddrives");
        //sqlserver2008 实测
        // drive    MB 可用空间
        // C        57958
        // sqlsever2022 实测
        // drive    MB free
        // C        64123
        // D        125623
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            info.DriverRemainSpaces.Add((dt.Rows[i][0].ToString(), long.Parse(dt.Rows[i][1].ToString())));
        }
        //sp_server_info
        dt = await db.SelectDataTableAsync("Exec sp_server_info");
        //sqlsever2008 实测
        // attribute_id    attribute_name             attribute_value
        // 1               DBMS_NAME                  Microsoft SQL Server
        // 2               DBMS_VER                   Microsoft SQL Server 2008 R2 - 10.50.1600.1
        // 10              OWNER_TERM                 owner
        // 11              TABLE_TERM                 table
        // 12              MAX_OWNER_NAME_LENGTH      128
        // 13              TABLE_LENGTH               128
        // 14              MAX_QUAL_LENGTH            128
        // 15              COLUMN_LENGTH              128
        // 16              IDENTIFIER_CASE            MIXED
        // 17              TX_ISOLATION               2
        // 18              COLLATION_SEQ              charset=cp936 collation=Chinese_PRC_CI_AS
        // 19              SAVEPOINT_SUPPORT          Y
        // 20              MULTI_RESULT_SETS          Y
        // 22              ACCESSIBLE_TABLES          Y
        // 100             USERID_LENGTH              128
        // 101             QUALIFIER_TERM             database
        // 102             NAMED_TRANSACTIONS         Y
        // 103             SPROC_AS_LANGUAGE          Y
        // 104             ACCESSIBLE_SPROC           Y
        // 105             MAX_INDEX_COLS             16
        // 106             RENAME_TABLE               Y
        // 107             RENAME_COLUMN              Y
        // 108             DROP_COLUMN                Y
        // 109             INCREASE_COLUMN_LENGTH     Y
        // 110             DDL_IN_TRANSACTION         Y
        // 111             DESCENDING_INDEXES         Y
        // 112             SP_RENAME                  Y
        // 113             REMOTE_SPROC               Y
        // 500             SYS_SPROC_VERSION          10.50.1600
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var attr_name = dt.Rows[i]["attribute_name"].ToString();
            var attr_value = dt.Rows[i]["attribute_value"].ToString();
            if (attr_name == "DBMS_NAME")
            {
                info.DBMS_NAME = attr_value;
            }
            else if (attr_name == "DBMS_VER")
            {
                info.DBMS_VER = attr_value;
            }
            else if (attr_name == "COLLATION_SEQ")
            {
                info.Collection_Seq = attr_value;
            }
            else if (attr_name == "SYS_SPROC_VERSION")
            {
                info.SYS_SPROC_VERSION = attr_value;
            }
        }
        //数据库版本信息
        info.FullVersion = await db.SelectScalarAsync<string>("select @@VERSION");

        //sp_spaceused
        var ds = await db.SelectDataSetAsync("exec sp_spaceused");
        //执行返回两张表
        //第一张表: sqlserver2008 实测
        // database_name    database_size    unallocated space
        // test             6.75 MB          1.43 MB
        // 
        //第二张表: sqlserver2008 实测
        // reserved         data             index_size           unused
        // 2632 KB          1248 KB          1240 KB              144 KB
        info.DBName = ds.Tables[0].Rows[0]["databse_name"].ToString();
        info.DBSize = ds.Tables[0].Rows[0]["database_size"].ToString();
        info.UnAllocatedSize = ds.Tables[0].Rows[0]["unallocated space"].ToString();
        info.Reserved = ds.Tables[1].Rows[0]["reserved"].ToString();
        info.DataSize = ds.Tables[1].Rows[0]["data"].ToString();
        info.IndexSize = ds.Tables[1].Rows[0]["index_size"].ToString();
        info.UnUsedSize = ds.Tables[1].Rows[0]["unused"].ToString();

        dt = await db.SelectDataTableAsync("DBCC SQLPERF(LOGSPACE)");
        //sqlsever2008 实测 注意: 这里显示的日志占用空间已包含在了 sp_spaceused 的数据库总空间里
        // Database Name    Log Size (MB)    Log Space Used (%)    Status
        // master           1.242188         66.03773              0
        // tempdb           0.7421875        57.69737              0
        // model            0.7421875        46.31579              0
        // msdb             3.054688         30.69054              0
        // test             2.742188         68.75                 0
        // test2            0.9921875        38.38583              0
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var dbname = dt.Rows[i]["Database Name"].ToString();
            var logsize = dt.Rows[i][1].ToString();
            var logpercent = dt.Rows[i][2].ToString();
            if (dbname == info.DBName)
            {
                info.LogStat = (double.Parse(logsize), double.Parse(logpercent));
            }
        }
        //select * from sys.databases
        info.CreateTime = await db.SelectScalarAsync<DateTime>($"select create_date from sys.databases where name={db.ProtectStringToSeg(info.DBName)}");
        return info;
    }
    #endregion

    #region 显示文件组和日志信息 ShowFileGroups()
    /// <summary>
    /// 显示文件组和日志信息
    /// </summary>
    /// <returns></returns>
    public (List<SqlServerFileGroup> FileGroups, List<SqlServerFile> LogFiles) ShowFileGroups()
    {
        List<SqlServerFileGroup> groups = null;
        List<SqlServerFile> files = null;
        groups = db.SelectModelList<SqlServerFileGroup>("select * from sys.filegroups");
        files = db.SelectModelList<SqlServerFile>("select * from sys.database_files");
        for (int i = files.Count - 1; i >= 0; i--)
        {
            var file = files[i];
            var group = groups.Find(g => g.Data_Space_Id == file.Data_Space_Id);
            if (group != null)
            {
                group.Files.Add(file);
                files.RemoveAt(i);
            }
        }
        return (groups, files);
    }
    #endregion
}
